package com.vanke.vms.management.dao.daoImpl;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.vanke.vms.common.domain.AddInfo;
import com.vanke.vms.common.domain.ForXls;
import com.vanke.vms.management.dao.XlsOpDao;

public class XlsOpDaoImpl extends HibernateDaoSupport implements XlsOpDao {
	public String importInfo(List<ForXls> infoHeadList,
			List<List<String>> infoValueList, List<ForXls> addInfoHeadList,
			List<List<String>> addInfoValueList) {
		// ///////insert info

		// List<Object[]> list = session.createSQLQuery(sql).addScalar("pname",
		// Hibernate.STRING).addScalar("cname", Hibernate.STRING).list();
		// session.close();
		int i1 = 0;
		int i2 = 1;
		int i3 = 2;// 分别是houseSet,houseBuild,houseNumber的ID
		for (int i = 0; i < infoHeadList.size(); i++) {
			if (infoHeadList.get(i).getPname().equals("houseSet")) {
				i1 = i;
			} else if (infoHeadList.get(i).getPname().equals("houseBuild")) {
				i2 = i;
			} else if (infoHeadList.get(i).getPname().equals("houseNumber")) {
				i3 = i;
			}
		}
		
		////////////////////是所有的sql只要一个session来Open，最后close就行呢还是每一个SQL都要OPEN一个SESSION?
		SessionFactory sessionfactory = getHibernateTemplate()
				.getSessionFactory();
		Session session = sessionfactory.openSession();
		
		List<Integer> infoIdList = new ArrayList<Integer>();
		for (int i = 0; i < infoValueList.size(); i++) {
			String checkSQL = "select inf.infoId from vms.info as inf where inf.houseSet = '"
					+ infoValueList.get(i).get(i1) + "' and inf.houseBuild = '"
					+ infoValueList.get(i).get(i2)
					+ "' and inf.houseNumber = '"
					+ infoValueList.get(i).get(i3) + "'";
			List<Integer> returninfoidList = (List<Integer>)session.createSQLQuery(checkSQL).list();
			
			if (returninfoidList.size() > 0) {//////// update
				Integer updateid = returninfoidList.get(0);

				String updateSQL = "update vms.info set ";
				for(int j=0;j<infoHeadList.size()-1;j++){
					updateSQL += infoHeadList.get(j).getPname();
					updateSQL += "='";
					updateSQL += infoValueList.get(i).get(j);
					updateSQL += "',";
				}
				updateSQL += infoHeadList.get(infoHeadList.size()-1).getPname();
				updateSQL += "='";
				updateSQL += infoValueList.get(i).get(infoHeadList.size()-1);
				updateSQL += "' where infoId=";
				updateSQL += updateid.toString();
				session.createSQLQuery(updateSQL).executeUpdate();
				
				//////////////////////////////////addInfo
				for(int j=0; j<addInfoHeadList.size();j++){
					String checkAdd = "select adi.addinfoid from vms.addinfo as adi where adi.infoid = " +
							updateid.toString() + " and adi.pname = '" +
							addInfoHeadList.get(j).getPname()+"' and adi.cname= '" +
							addInfoHeadList.get(j).getCname()+"'";
					List<Integer> checkaddinfoidList = (List<Integer>)session.createSQLQuery(checkAdd).list();
					if(checkaddinfoidList.size() > 0){
						//update addinfo
						Integer curAddInfoId = checkaddinfoidList.get(0);
//						String updateAdd = "update vms.addinfo set content = '" +
//							addInfoValueList.get(i).get(j)+"' where infoid = " +
//							updateid.toString()+" and pname = '" +
//							addInfoHeadList.get(j).getPname() + "' and cname = '" +
//							addInfoHeadList.get(j).getCname()+"'";
						String updateAdd = "update vms.addinfo set content = '" +
							addInfoValueList.get(i).get(j)+"' where addinfoid = " +
							curAddInfoId.toString();
						session.createSQLQuery(updateAdd).executeUpdate();
					}
					else{//insert addinfo
						String insertAdd = "insert into vms.addinfo(infoid,pname,cname,content) values(" +
							updateid.toString()+",'"+addInfoHeadList.get(j).getPname()+"','" +
							addInfoHeadList.get(j).getCname()+"','"+addInfoValueList.get(i).get(j)+"')";
						session.createSQLQuery(insertAdd).executeUpdate();
					}
				}
				
			} else {/////// insert
				String insertSQL = "insert into vms.info(";
				for(int j=0;j<infoHeadList.size()-1;j++){
					insertSQL += infoHeadList.get(j).getPname();
					insertSQL += ",";
				}
				insertSQL += infoHeadList.get(infoHeadList.size()-1).getPname();
				insertSQL += ") values('";
				for(int j=0;j<infoHeadList.size()-1;j++){
					insertSQL += infoValueList.get(i).get(j);
					insertSQL += "','";
				}
				insertSQL += infoValueList.get(i).get(infoHeadList.size()-1);
				insertSQL += "')";// SELECT @@IDENTITY AS returnName";
				//List<Integer> insertInfoidList = (List<Integer>)session.createSQLQuery(insertSQL).list();
//				Integer insertInfoId = 
				session.createSQLQuery(insertSQL).executeUpdate();
				//插入进去以后再查找infoId.....不知道用那个函数
				
				//List<Integer> tmp = (List<Integer>)session.createSQLQuery(checkSQL).list();
				
				//////////////////////////////addInfo
				Integer curInfoId = ((List<Integer>)session.createSQLQuery(checkSQL).list()).get(0);
				for(int j=0; j<addInfoHeadList.size();j++){
					String insertAdd = "insert into vms.addinfo(infoid,pname,cname,content) values(" +
						curInfoId.toString()+",'"+addInfoHeadList.get(j).getPname()+"','" +
						addInfoHeadList.get(j).getCname()+"','"+addInfoValueList.get(i).get(j)+"')";
					session.createSQLQuery(insertAdd).executeUpdate();
				}
			}

			
		}
		
		session.close();

		return "success";
	}
}
